package com.zm.common.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;

/**
 * ExcelUtil 工具类
 * @author DENG.ZHUO.HUI
 * @email 13751827792@163.com
 * @date 2017年2月21日
 * @version 1.0
 */
public class ExcelUtil {
	
	/**
	 * 解析Excel文件，封装并返回Excel数据
	 * @param excel
	 * @return excelData
	 * @throws Exception
	 */
	public static List<Map<Integer, Object>> parseExcel(File excel)throws Exception {
		
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excel));
		
		HSSFSheet sheet = workbook.getSheetAt(0);
		
		int lastRowIndex = sheet.getLastRowNum();
		
		List<Map<Integer, Object>> excelData = new ArrayList<>();
		
		for(int i = 0; i <= lastRowIndex; i++){
			HSSFRow row = sheet.getRow(i);
			
			Iterator<Cell> cells = row.cellIterator();
			
			Map<Integer, Object> rowData = new HashMap<>();
			
			while(cells.hasNext()){
				Cell cell = cells.next();
				int columnIndex = cell.getColumnIndex();
				
				if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
					if(DateUtil.isCellDateFormatted(cell)){
						rowData.put(columnIndex, cell.getDateCellValue());
					}else{
						rowData.put(columnIndex, cell.getNumericCellValue());
					}
				}else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
					rowData.put(columnIndex, cell.getBooleanCellValue());
				}else {
					rowData.put(columnIndex, cell.getStringCellValue());
				}
			}
			excelData.add(rowData);
		}
		workbook.close();
		return excelData;
	}
	
	/**
	 * 解析Excel文件，封装并返回Excel数据
	 * @param excel
	 * @return excelData
	 * @throws Exception
	 */
	public static List<Map<String, Object>> parseExcel(InputStream excel)throws Exception {
		
		HSSFWorkbook workbook = new HSSFWorkbook(excel);
		
		HSSFSheet sheet = workbook.getSheetAt(0);
		
		int lastRowIndex = sheet.getLastRowNum();
		
		List<Map<String, Object>> excelData = new ArrayList<>();
		
		for(int i = 0; i <= lastRowIndex; i++){
			HSSFRow row = sheet.getRow(i);
			
			Iterator<Cell> cells = row.cellIterator();
			
			Map<String, Object> rowData = new HashMap<>();
			
			while(cells.hasNext()){
				Cell cell = cells.next();
				String columnIndex = cell.getColumnIndex() + "";
				
				if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
					if(DateUtil.isCellDateFormatted(cell)){
						rowData.put(columnIndex, cell.getDateCellValue());
					}else{
						double numericCellValue = cell.getNumericCellValue(); 
						String val = String.valueOf(numericCellValue);
				        if (val.lastIndexOf(".0") > -1) {  
				            String stringValue = val.substring(0, val.length() - 2).trim();  
				            rowData.put(columnIndex, stringValue);
				        } else {  
				            Double doubleValue = Double.valueOf(val);
				            rowData.put(columnIndex, doubleValue);
				        }
					}
				}else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
					rowData.put(columnIndex, cell.getBooleanCellValue());
				}else {
					rowData.put(columnIndex, cell.getStringCellValue());
				}
			}
			excelData.add(rowData);
		}
		workbook.close();
		return excelData;
	}
	
	public static void exportExcelByObj(String[] titles,String sheetName,
				String excelName,List<Object[]> data,HttpServletResponse response) throws IOException{
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(sheetName);
		
		HSSFRow row = sheet.createRow(0);
		for(int i=0; i < titles.length; i++){
			row.createCell(i).setCellValue(titles[i]);
		}
		
		for(int i=0; i < data.size(); i++){
			HSSFRow dataRow = sheet.createRow(i + 1);
			Object[] values = data.get(i);
			for(int j=0; j < values.length; j++){
				dataRow.createCell(j).setCellValue(values[j].toString());
			}
		}
		
		response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(excelName,"utf-8")+".xls");
		workbook.write(response.getOutputStream());
		workbook.close();
	}
	
	public static void exportExcelByMap(String[] titles,String sheetName,
			String excelName,List<Map<String, Object>> data,HttpServletRequest request,HttpServletResponse response) throws IOException{
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(sheetName);
		
		HSSFRow row = sheet.createRow(0);
		for(int i=0; i < titles.length; i++){
			row.createCell(i).setCellValue(titles[i]);
		}
		
		for(int i=0; i < data.size(); i++){
			HSSFRow dataRow = sheet.createRow(i + 1);
			Map<String, Object> map = data.get(i);
			for(int j=0; j < titles.length; j++){
				dataRow.createCell(j).setCellValue(map.get(titles[j]).toString());
			}
		}
		
		String userAgent = request.getHeader("user-agent");
		System.out.println(userAgent);
		if (userAgent.toLowerCase().indexOf("msie") != -1){
			excelName = new String(excelName.getBytes("gbk"), "iso8859-1");
		}else if(userAgent.toLowerCase().indexOf("firefox") != -1){
			excelName = new String(excelName.getBytes("utf-8"), "iso8859-1");
		}else{
			excelName = URLEncoder.encode(excelName,"utf-8");
		}
		
		response.setHeader("Content-Disposition", "attachment;filename=" + excelName + ".xls");
		workbook.write(response.getOutputStream());
		workbook.close();
	}
	
	public static void exportExcelByBean(String[] titles,String sheetName,
			String excelName,List<?> data,HttpServletRequest request,HttpServletResponse response) throws Exception{
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(sheetName);
		
		HSSFRow row = sheet.createRow(0);
		for(int i=0; i < titles.length; i++){
			row.createCell(i).setCellValue(titles[i]);
		}
		
		for(int i=0; i < data.size(); i++){
			HSSFRow dataRow = sheet.createRow(i + 1);
			Object obj = data.get(i);
			Field[] fields = obj.getClass().getDeclaredFields();
			for(int j=0; j < fields.length; j++){
				Field field = fields[j];
				field.setAccessible(true);
				Object value = field.get(obj);
				dataRow.createCell(j).setCellValue(value == null ? "" : value.toString());
			}
		}
		
		String userAgent = request.getHeader("user-agent");
		System.out.println(userAgent);
		if (userAgent.toLowerCase().indexOf("msie") != -1){
			excelName = new String(excelName.getBytes("gbk"), "iso8859-1");
		}else if(userAgent.toLowerCase().indexOf("firefox") != -1){
			excelName = new String(excelName.getBytes("utf-8"), "iso8859-1");
		}else{
			excelName = URLEncoder.encode(excelName,"utf-8");
		}
		
		response.setHeader("Content-Disposition", "attachment;filename=" + excelName + ".xls");
		workbook.write(response.getOutputStream());
		workbook.close();
	}
	
	/** 
	 * 
	 *  */
	public static void exportExcelByBean(String[] titles,String[] fieldNames,String sheetName,
			String excelName,List<?> data,HttpServletRequest request,HttpServletResponse response) throws Exception{
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(sheetName);
		
		HSSFRow row = sheet.createRow(0);
		for(int i=0; i < titles.length; i++){
			row.createCell(i).setCellValue(titles[i]);
		}
		
		for(int i=0; i < data.size(); i++){
			HSSFRow dataRow = sheet.createRow(i + 1);
			Object obj = data.get(i);
			Class<? extends Object> clazz = obj.getClass();
			
			for(int j=0; j < fieldNames.length; j++){
				String fieldName = fieldNames[j];
				fieldName = fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
				Method method = clazz.getDeclaredMethod("get"+fieldName);
				Object value = method.invoke(obj);
				dataRow.createCell(j).setCellValue(value.toString());
			}
			
		}
		
		String userAgent = request.getHeader("user-agent");
		System.out.println(userAgent);
		if (userAgent.toLowerCase().indexOf("msie") != -1){
			excelName = new String(excelName.getBytes("gbk"), "iso8859-1");
		}else if(userAgent.toLowerCase().indexOf("firefox") != -1){
			excelName = new String(excelName.getBytes("utf-8"), "iso8859-1");
		}else{
			excelName = URLEncoder.encode(excelName,"utf-8");
		}
		
		response.setHeader("Content-Disposition", "attachment;filename=" + excelName + ".xls");
		workbook.write(response.getOutputStream());
		workbook.close();
	}

}
